package com.kehutong.payfees.service;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

import com.kehutong.common.util.Token;
import com.kehutong.payfees.util.BasicClient;
import com.kehutong.payfees.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.mvc.http.ReqMapping;
import org.coraframework.mvc.webservice.WebService;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.util.Objects;

import com.kehutong.common.errcode.Result;
import com.kehutong.common.math.CalculatorUtils;
import com.kehutong.common.urlconfig.FileUrlConfig;
import com.kehutong.payfees.entity.ChargeChoose;
import com.kehutong.payfees.entity.ChargeMonthRead;
import com.kehutong.payfees.entity.ChargeUserInstrument;
import com.kehutong.payfees.enums.UserInstrumentMaxRead;

@WebService("/payfees/charge/user/instrument/service")
public class ChargeUserInstrumentServiceImpl {

    @Inject
    private JdbcSession sqlSession;
    @Inject
    private BasicClient basicClient;
    @Inject
    private FileUrlConfig fileUrlConfig;

    public Object list(Token token, JSONObject jsonObject) throws Exception {
        Page<ChargeUserInstrument> chargeUserInstrumentPage = sqlSession.findPage(ChargeUserInstrument.class)
                .eq(jsonObject, "communityNo", "houseId", "instrumentTypeId", "instrumentNo")
                .like(jsonObject, "contactName")
                .eq("deleted", false)
                .order("createTime", "desc")
                .page(jsonObject)
                .exe();

        List<ChargeUserInstrument> userInstrumentList = chargeUserInstrumentPage.getList();
        List<String> houseIds = userInstrumentList.stream().map(ChargeUserInstrument::getHouseId).collect(Collectors.toList());
        Map<String, JSONObject> houseMap = basicClient.getHouseByIdsMap(token, new ArrayList<>(houseIds));

        userInstrumentList.forEach(item -> {
            JSONObject json = houseMap.get(item.getHouseId());
            if(Objects.nonNull(json)){
                item.setBuildNo(json.getString("buildNo"));
                String unitName = json.getString("unitName");
                String buildName = json.getString("buildName");
                item.setBuildName(Objects.nonEmpty(unitName) ? buildName + "-" + unitName : buildName );
                item.setHouseName(json.getString("floorName") + json.getString("name"));
                item.setHouseInfo(basicClient.getHouseInfo(json));
            }

        });
        return chargeUserInstrumentPage;
    }

    @ReqMapping("/listAll")
    public Object listAll(JSONObject jsonObject){
        List<ChargeUserInstrument> list = sqlSession.findArray(ChargeUserInstrument.class)
                .eq(jsonObject, "communityNo", "houseId", "instrumentTypeId", "instrumentNo")
                .like(jsonObject, "contactName")
                .eq("deleted", false)
                .order("createTime", "desc")
                .exe();
        return list;
    }

    public void save(Token token, ChargeUserInstrument chargeUserInstrument) throws Exception {
        chargeUserInstrument.setCopyInstrument(true);

        setDepartmentNo(chargeUserInstrument, token.getCompanyNo());

        sqlSession.insert(chargeUserInstrument);
    }

    public void update(Token token, ChargeUserInstrument chargeUserInstrument) throws Exception {
        ChargeUserInstrument resultChargeUserInstrument = Pool.get(ChargeUserInstrument.class, chargeUserInstrument.getId());
        if (!resultChargeUserInstrument.getInstrumentNo().equals(chargeUserInstrument.getInstrumentNo())) {
            List<ChargeChoose> chargeChooseList = sqlSession.findArray(ChargeChoose.class)
                    .eq("communityNo", chargeUserInstrument.getCommunityNo())
                    .eq("instrumentId", chargeUserInstrument.getId())
                    .eq("deleted", false).exe();

            chargeChooseList.forEach(item -> item.setInstrumentNo(chargeUserInstrument.getInstrumentNo()));
            sqlSession.updateById(chargeChooseList);

            List<ChargeMonthRead> chargeMonthReadList = sqlSession.findArray(ChargeMonthRead.class)
                    .eq("communityNo", chargeUserInstrument.getCommunityNo())
                    .eq("instrumentId", chargeUserInstrument.getId())
                    .eq("deleted", false).exe();

            chargeMonthReadList.forEach(item -> item.setInstrumentNo(chargeUserInstrument.getInstrumentNo()));
            sqlSession.updateById(chargeMonthReadList);
        }

        setDepartmentNo(chargeUserInstrument, token.getCompanyNo());

        sqlSession.updateById(chargeUserInstrument);
    }

    private void setDepartmentNo(ChargeUserInstrument chargeUserInstrument, String companyNo) throws Exception {
        JSONObject community = basicClient.getCommunityById(companyNo,
                chargeUserInstrument.getCommunityNo());
        if(Objects.nonEmpty(community)){
            String parent_id = community.getString("parent_id");
            if(Objects.nonEmpty(parent_id)){
                chargeUserInstrument.setDepartmentNo(parent_id);
            }
            JSONArray parent_ids = community.getJSONArray("parent_ids");
            if(Objects.nonEmpty(parent_ids)){
                List<String> departmentNos = JSONObject.parseArray(parent_ids.toJSONString(), String.class);
                chargeUserInstrument.setDepartmentNos(departmentNos);
            }
        }
    }

    public void delete(String id) {
        ChargeUserInstrument chargeUserInstrument = Pool.get(ChargeUserInstrument.class, id);
        if (Objects.nonNull(chargeUserInstrument)) {
            chargeUserInstrument.setDeleted(true);
            sqlSession.updateById(chargeUserInstrument);
        }
    }

    public void setCopyInstrument(String id, boolean copyInstrument) {
        ChargeUserInstrument chargeUserInstrument = Pool.get(ChargeUserInstrument.class, id);
        if (Objects.nonNull(chargeUserInstrument)) {
            chargeUserInstrument.setCopyInstrument(copyInstrument);
            sqlSession.updateById(chargeUserInstrument);
        }
    }

    public void setAllCopyInstrument(JSONObject jsonObject) {
        List<ChargeUserInstrument> chargeUserInstruments = sqlSession.findArray(ChargeUserInstrument.class)
                .eq(jsonObject, "communityNo", "instrumentTypeId")
                .eq("deleted", false).exe();

        chargeUserInstruments.forEach(item -> {
            item.setCopyInstrument(jsonObject.getBoolean("copyInstrument"));
        });

        sqlSession.updateById(chargeUserInstruments);
    }

    public void deleteUserInstrumentByIds(JSONArray jsonArray) {
        jsonArray.forEach(id -> {
            ChargeUserInstrument chargeUserInstrument = Pool.get(ChargeUserInstrument.class, id.toString());
            chargeUserInstrument.setDeleted(true);
            sqlSession.updateById(chargeUserInstrument);
        });
    }

    public File downloadUserInstrument(Token token, JSONObject jsonObject) throws Exception {
        JSONArray communityNos = new JSONArray();
        communityNos.add(jsonObject.getString("communityNo"));
        jsonObject.put("communityNo", communityNos);

        Map<String,JSONObject> communityMap = basicClient.getCommunityMap(token);

        JSONArray data = basicClient.getHouses(token, jsonObject);
        Workbook wb = generateTemplateExportWorkBook(communityMap, data);
        return ExcelUtil.writeXlsFile(wb, "仪表导入模板", ".xls");
    }

    public Object readUserInstrumentFile(Token token, JSONObject jsonObject) throws Exception {
        int cellCountNumber = 8;
        byte[] bs = fileUrlConfig.download("/file/download", http->http.addParam("id", jsonObject.getString("id")));
        List<String[]> rowList = ExcelUtil.readExcel(new ByteArrayInputStream(bs), cellCountNumber);

        for (int i = 1; i < rowList.size(); i++) {
            String[] rowData = rowList.get(i);
            ChargeUserInstrument chargeUserInstrument = Pool.newInstance(ChargeUserInstrument.class);
            Object checkArgsResult = checkArgsAndGiveValue(token, rowData, i, chargeUserInstrument, jsonObject);
            if (Objects.nonNull(checkArgsResult)) {
                return checkArgsResult;
            }

            chargeUserInstrument.setCommunityNo(jsonObject.getString("communityNo"));
            chargeUserInstrument.setInstrumentTypeId(jsonObject.getString("instrumentTypeId"));
            chargeUserInstrument.setDeleted(false);
            chargeUserInstrument.setRatio(new BigDecimal(1));

            setDepartmentNo(chargeUserInstrument, token.getCompanyNo());

            sqlSession.insert(chargeUserInstrument);
        }
        return Result.success("上传成功");
    }

    public File exportUserInstrumentFile(Token token, JSONObject jsonObject) throws Exception {
        List<ChargeUserInstrument> chargeUserInstrumentList = sqlSession.findArray(ChargeUserInstrument.class)
                .eq(jsonObject, "communityNo", "instrumentTypeId", "houseId", "instrumentNo")
                .like(jsonObject, "contactName")
                .eq("deleted", false).exe();

        Workbook wb = exportWorkBook(token, chargeUserInstrumentList);
        return ExcelUtil.writeXlsFile(wb, "入户表管理列表", ".xls");
    }


    private Workbook generateTemplateExportWorkBook(Map<String,JSONObject> communityMap, JSONArray data) {

        // 生成模板文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工具
        CreationHelper helper = wb.getCreationHelper();
        //单元格样式类
        CellStyle dateCellStyle = wb.createCellStyle();
        //设置单元格格式
        dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));

        HSSFCellStyle yellowStyle = wb.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle chocolateStyle = wb.createCellStyle();
        chocolateStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
        chocolateStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Sheet sheet = wb.createSheet();
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 7));

        // 首行 带颜色的说明文字
        Row firstRow = sheet.createRow(0);
        Cell firstRowCell = firstRow.createCell(0);
        firstRowCell.setCellValue("只读字段");
        firstRowCell.setCellStyle(yellowStyle);

        firstRowCell = firstRow.createCell(4);
        firstRowCell.setCellValue("填写字段（读数日期的格式为" + LocalDate.now() + "，初始/最近读数最多支持一位小数）");
        firstRowCell.setCellStyle(chocolateStyle);

        Row headerRow = sheet.createRow(1);
        int headerCellNum = 0;
        String[] chargeUserInstrument = {"房屋id", "小区信息", "房屋信息", "当前客户", "仪表编号", "最大读数", "初始/最近读数", "读数日期", "是否抄表"};
        for (String s : chargeUserInstrument) {
            Cell cell = headerRow.createCell(headerCellNum++);
            cell.setCellValue(s);
        }

//        //下拉列表
//        CellRangeAddressList addressList1 = null;
//        XSSFDataValidation dataValidation1 = null;
//
//        CellRangeAddressList addressList2 = null;
//        XSSFDataValidation dataValidation2 = null;

        Object[] objects = Arrays.stream(UserInstrumentMaxRead.values()).map(UserInstrumentMaxRead::getDesc).toArray();
        String[] maxReadList = Arrays.copyOf(objects, objects.length, String[].class);

        //生成下拉列表
        // 只对（2，200）单元格有效
        CellRangeAddressList maxReadRegions = new CellRangeAddressList(2, 200, 5, 5);
        //生成下拉框内容
        DVConstraint maxReadConstraint = DVConstraint.createExplicitListConstraint(maxReadList);
        //绑定下拉框和作用区域
        HSSFDataValidation maxReadDataValidation = new HSSFDataValidation(maxReadRegions, maxReadConstraint);
        //对sheet页生效
        sheet.addValidationData(maxReadDataValidation);

        String[] copyInstrumentList = {"是", "否"};
        //生成下拉列表
        // 只对（2，200）单元格有效
        CellRangeAddressList copyInstrumentRegions = new CellRangeAddressList(2, 200, 8, 8);
        //生成下拉框内容
        DVConstraint copyInstrumentConstraint = DVConstraint.createExplicitListConstraint(copyInstrumentList);
        //绑定下拉框和作用区域
        HSSFDataValidation copyInstrumentDataValidation = new HSSFDataValidation(copyInstrumentRegions, copyInstrumentConstraint);
        //对sheet页生效
        sheet.addValidationData(copyInstrumentDataValidation);

        int cellNum = 0;
        ChargeUserInstrument initChargeUserInstrument = Pool.newInstance(ChargeUserInstrument.class);
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 2);
            cellNum = 0;
            JSONObject h = (JSONObject) data.get(i);
            row.createCell(cellNum++).setCellValue(h.getString("id"));
            row.createCell(cellNum++).setCellValue(communityMap.get(h.getString("communityNo")).getString("name"));
            row.createCell(cellNum++).setCellValue(basicClient.getHouseInfo(h));
            row.createCell(cellNum++).setCellValue(h.getString("ownerName"));

            setCellValue(row, cellNum, initChargeUserInstrument, dateCellStyle);
        }

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            if (i == 0) {
                // id列不调整
                continue;
            }
            // 自动列宽
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    private void setCellValue(Row row, int cellNum, ChargeUserInstrument chargeUserInstrument, CellStyle dateCellStyle) {
        row.createCell(cellNum++).setCellValue(chargeUserInstrument.getInstrumentNo());
        row.createCell(cellNum++).setCellValue(Objects.nonNull(chargeUserInstrument.getMaxRead()) ? String.valueOf(chargeUserInstrument.getMaxRead()) : "" );
        row.createCell(cellNum++).setCellValue(Objects.nonNull(chargeUserInstrument.getLateRead()) ? String.valueOf(chargeUserInstrument.getLateRead()) : "");
        Cell tmpCell = row.createCell(cellNum++);
        tmpCell.setCellStyle(dateCellStyle);
        tmpCell.setCellValue(chargeUserInstrument.getReadDate());
        row.createCell(cellNum++).setCellValue(chargeUserInstrument.isCopyInstrument() ? "是" : "否");
    }

    private Workbook exportWorkBook(Token token, List<ChargeUserInstrument> chargeUserInstrumentList) throws Exception {

        // 生成模板文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工具
        CreationHelper helper = wb.getCreationHelper();
        //单元格样式类
        CellStyle dateCellStyle = wb.createCellStyle();
        //设置单元格格式
        dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));

        Sheet sheet = wb.createSheet();
        Row headerRow = sheet.createRow(0);
        int headerCellNum = 0;
        String[] chargeUserInstrument = {"房屋id", "房屋信息", "当前客户", "仪表编号", "最大读数", "初始/最近读数", "读数日期", "是否抄表"};
        for (String s : chargeUserInstrument) {
            Cell cell = headerRow.createCell(headerCellNum++);
            cell.setCellValue(s);
        }

        List<String> houseIds = chargeUserInstrumentList.stream().map(ChargeUserInstrument::getHouseId).collect(Collectors.toList());
        Map<String, JSONObject> mapData = basicClient.getHouseByIdsMap(token, houseIds);

        int cellNum = 0;
        for (int i = 0; i < chargeUserInstrumentList.size(); i++) {
            JSONObject h = mapData.get(chargeUserInstrumentList.get(i).getHouseId());
            Row row = sheet.createRow(i + 1);
            cellNum = 0;
            row.createCell(cellNum++).setCellValue(h.getString("id"));
            row.createCell(cellNum++).setCellValue(basicClient.getHouseInfo(h));
            row.createCell(cellNum++).setCellValue(chargeUserInstrumentList.get(i).getContactName());
            row.createCell(cellNum++).setCellValue(chargeUserInstrumentList.get(i).getInstrumentNo());

            String maxRead = String.valueOf(chargeUserInstrumentList.get(i).getMaxRead() == null ? "" : chargeUserInstrumentList.get(i).getMaxRead().getDesc());
            row.createCell(cellNum++).setCellValue(maxRead.equals("0") ? "" : maxRead);

            String lateRead =  new BigDecimal(String.valueOf(chargeUserInstrumentList.get(i).getLateRead())).setScale(1, BigDecimal.ROUND_HALF_UP).toString();
            row.createCell(cellNum++).setCellValue(lateRead.equals("0") ? "" : lateRead);

            Cell tmpCell = row.createCell(cellNum++);
            tmpCell.setCellStyle(dateCellStyle);
            tmpCell.setCellValue(chargeUserInstrumentList.get(i).getReadDate());
            row.createCell(cellNum++).setCellValue(chargeUserInstrumentList.get(i).isCopyInstrument() ? "是" : "否");
        }

        // 自动列宽
        for (int i = 0; i < cellNum; i++) {
            if (i == 0) {
                // id列不调整
                continue;
            }
            // 自动列宽
            ExcelUtil.autoSizeColumn(sheet, i);
        }
        return wb;
    }

    private Object checkArgsAndGiveValue(Token token, String[] rowData, int i, ChargeUserInstrument chargeUserInstrument, JSONObject jsonObject) throws Exception {
        if(Objects.isBlank(rowData[0]) || Objects.isBlank(rowData[1]) || Objects.isBlank(rowData[2]) || Objects.isBlank(rowData[4])
                || Objects.isBlank(rowData[5]) || Objects.isBlank(rowData[6]) || Objects.isBlank(rowData[7])|| Objects.isBlank(rowData[8])){
            return Result.error(0, "第"+(i+2)+"行，单元格没有填写数据,请检查!");
        }

        String houseId = rowData[0];

        JSONObject data = basicClient.getHouseById(token, houseId);
        if (Objects.isNull(data)) {
            return Result.error(0, "第"+(i+2)+"行，房屋id请勿修改!");
        }

        String houseName = rowData[2];
        if (!houseName.equals(basicClient.getHouseInfo(data))) {
            return Result.error(0, "第"+(i+2)+"行，房屋信息请勿修改!");
        }

        String readMax = rowData[5];
        if (!Arrays.stream(UserInstrumentMaxRead.values()).map(UserInstrumentMaxRead::getDesc).collect(Collectors.toList()).contains(readMax)) {
            return Result.error(-1, "请选择第"+(i+2)+"行，最大读数的数值！");
        }

        BigDecimal lateRead = new BigDecimal(rowData[6]);
        if (CalculatorUtils.getDecimalPlace(lateRead) > 1) {
            return Result.error(-1, "请输入第"+(i+2)+"行，初始/最近读数为最多为一位小数！");
        }

        String readDate = "";
        try{
            readDate = LocalDate.parse("1900-01-01").plusDays(Long.valueOf(rowData[7])-2).toString();
        }catch (Exception e) {
            return Result.error(-1, "请输入第"+(i+2)+"行，正确的日期格式！");
        }

        String copyInstrument = rowData[8];
        if (!(copyInstrument.equals("是") || copyInstrument.equals("否"))) {
            return Result.error(-1, "请选择第"+(i+2)+"行，是否抄表！");
        }

        if (new BigDecimal(readMax).compareTo(lateRead) == -1) {
            return Result.error(0, "请检查第"+(i+2)+"行，当前仪表读数已经超过最大读数");
        }

        // 对象赋值
        ChargeUserInstrument resultUserInstrument = sqlSession.findOne(ChargeUserInstrument.class)
                .eq("communityNo", jsonObject.getString("communityNo"))
                .eq("instrumentNo", rowData[4])
                .eq("deleted", false).exe();
        if (Objects.nonNull(resultUserInstrument)) {
            return Result.error(0, "请检查第"+(i+2)+"行，当前仪表编号不能重复");
        }
        chargeUserInstrument.setInstrumentNo(rowData[4]);
        List<UserInstrumentMaxRead> userInstrumentMaxReads = Arrays.asList(UserInstrumentMaxRead.values());

        UserInstrumentMaxRead userInstrumentMaxRead = null;
        for (int j = 0; j < userInstrumentMaxReads.size(); j++) {
            if (userInstrumentMaxReads.get(j).getDesc().equals(readMax)) {
                userInstrumentMaxRead = userInstrumentMaxReads.get(j);
                break;
            }
        }
        chargeUserInstrument.setHouseId(houseId);
        chargeUserInstrument.setContactName(data.getString("ownerName"));
        chargeUserInstrument.setMaxRead(userInstrumentMaxRead);
        chargeUserInstrument.setLateRead(lateRead);
        chargeUserInstrument.setReadDate(readDate);
        chargeUserInstrument.setCopyInstrument(copyInstrument.equals("是"));

        return null;
    }

}
